/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package agenda.dao;

import agenda.bean.Contato;
import agenda.bean.Telefone;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;

/**
 *
 * @author Antonio
 */
public class TelefoneDAO {
    private Connection conn;

    public TelefoneDAO() {
        conn = Conexao.getInstance();
    }

    // insert
    public boolean insert(Telefone t) {
        String sql = "insert into telefone (id, contato, numero, descricao) "
                   + " values (nextval('seq_telefone'), ?, ?, ?) returning id";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, t.getContato().getId());
            ps.setString(2, t.getNumero());
            ps.setString(3, t.getDescricao());
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                t.setId(rs.getInt("id"));
                rs.close();
                return true;
            } else {
                return false;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
            JOptionPane.showMessageDialog(null, "Erro: " + ex.getMessage());
        }
        return false;
    }

    // update
    public boolean update(Telefone t) {
        String sql = "update telefone set contato = ?, numero = ?, "
                   + "descricao = ? where id = ?";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, t.getContato().getId());
            ps.setString(2, t.getNumero());
            ps.setString(3, t.getDescricao());
            ps.setObject(4, t.getId());
            if (ps.executeUpdate() > 0) {
                return true;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
            JOptionPane.showMessageDialog(null, "Erro: " + ex.getMessage());
        }
        return false;
    }

    // delete
    public boolean delete(Integer id) {
        String sql = "delete from telefone where id = ?";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setObject(1, id);
            if (ps.executeUpdate() > 0) {
                return true;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
            JOptionPane.showMessageDialog(null, "Erro: " + ex.getMessage());
        }
        return false;
    }

    // select
    public List<Telefone> select(String filtro) {
        if (filtro == null || filtro.trim().isEmpty()) {
            filtro = "1 = 1";
        }
        String sql = "select * from telefone where " + filtro;
        try {
            ResultSet rs = conn.createStatement().executeQuery(sql);
            List<Telefone> telefones = new ArrayList<Telefone>();
            while (rs.next()) {
                Telefone t = new Telefone(new Contato());
                t.setId(rs.getInt("id"));
                t.getContato().setId(rs.getInt("contato"));
                t.setNumero(rs.getString("numero"));
                t.setDescricao(rs.getString("descricao"));
                telefones.add(t);
            }
            rs.close();
            return telefones;
        } catch (SQLException ex) {
            ex.printStackTrace();
            JOptionPane.showMessageDialog(null, "Erro: " + ex.getMessage());
        }
        return null;
    }

    public String getSqlPesquisa() {
        String sql = "select c.id as \"ID\",                "
                   + "       c.nome as \"Nome\",            "
                   + "       tel.numero as \"Numero\",      "
                   + "       tel.descricao as \"Descricao\" "
                   + "  from contato c                                "
                   + "  left join telefone tel on c.id = tel.contato  "
                   + " where upper(c.nome || '%' || tel.numero) like upper(?)  "
                   + " order by c.nome, tel.numero";
        return sql;
    }


}


















